using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using RecruitmentSystem.Common;

namespace RecruitmentSystem.DataAccess
{
    public partial class UserAccessor
    {
        public bool AddPersonalUser(PersonalUser user)
        {
            const string SpName = "SpAddPersonalUser";

            try
            {
                object[] parameters = new object[]
                {
                    user.Username,
                    user.Password,
                    user.Question,
                    user.Answer,
                    user.Name,
                    user.Province,
                    user.District,
                    user.TelephoneNumber,
                    user.CellphoneNumber,
                    user.MailAddress,
                    user.Homepage,
                    user.PostCode,
                    user.Address,
                    user.Code,
                    user.Gender,
                    user.Nation,
                    user.Birthday,
                    user.IsMarriage,
                    user.Height,
                    user.Weight,
                    user.School,
                    user.Knowledge,
                    user.GraduateYear,
                    user.MajorCategory,
                    user.MajorName,
                    user.ResumeUrl,
                    user.CurrentStatu,
                    user.ProfessionaldescriptionAndSelfevaluation,
                    user.WorkexperienceAndDescription
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add personal user", ex);
            }

            return true;
        }

        public bool UpdatePasswordForPersonalUser(string newPassword, string oldPassword, string userId)
        {
            const string SpName = "SpChangePasswordByPersonalUserId";

            try
            {
                object[] parameters = new object[]
                {
                    newPassword,
                    oldPassword,
                    userId
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o != null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in change password for PersonalUser",
                    ex);
            }

            return true;
        }

        public PersonalUser GetPersonalUserById(string id)
        {
            const string SpGetPersonalUserById = "SpGetPersonalUser";
            PersonalUser user = null;
            SqlDataReader reader = null;
            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpGetPersonalUserById, new object[] { id });

                while (reader.Read())
                {
                    user = new PersonalUser();
                    user.Id = reader.GetInt32(0);
                    user.Username = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.Password = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Question = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Answer = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Name = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    user.Province = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    user.District = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    user.TelephoneNumber = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    user.CellphoneNumber = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    user.MailAddress = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    user.Homepage = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    user.PostCode = !reader.IsDBNull(12) ? reader.GetString(12) : null;
                    user.Address = !reader.IsDBNull(13) ? reader.GetString(13) : null;
                    user.Gender = reader.GetInt32(14);
                    user.Nation = !reader.IsDBNull(15) ? reader.GetString(15) : null;
                    user.Birthday = reader.GetDateTime(16);
                    user.IsMarriage = reader.GetBoolean(17);
                    user.Height = reader.GetDouble(18);
                    user.Weight = reader.GetDouble(19);
                    user.Code = !reader.IsDBNull(20) ? reader.GetString(20) : null;
                    user.School = !reader.IsDBNull(21) ? reader.GetString(21) : null;
                    user.Knowledge = !reader.IsDBNull(22) ? reader.GetString(22) : null;
                    user.GraduateYear = reader.GetInt32(23);
                    user.MajorCategory = !reader.IsDBNull(24) ? reader.GetString(24) : null;
                    user.MajorName = !reader.IsDBNull(25) ? reader.GetString(25) : null;
                    user.ResumeUrl = !reader.IsDBNull(26) ? reader.GetString(26) : null;
                    user.CurrentStatu = !reader.IsDBNull(27) ? reader.GetString(27) : null;
                    user.ProfessionaldescriptionAndSelfevaluation = !reader.IsDBNull(28) ? reader.GetString(28) : null;
                    user.WorkexperienceAndDescription = !reader.IsDBNull(29) ? reader.GetString(29) : null;
                    user.HeadPic = !reader.IsDBNull(30) ? reader.GetString(30) : null;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get personal user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return user;
        }

        public PersonalUser GetPersonalUserById(int id)
        {
            const string SpGetPersonalUserById = "SpGetPersonalUserByUserId";
            PersonalUser user = null;
            SqlDataReader reader = null;
            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpGetPersonalUserById, new object[] { id });

                while (reader.Read())
                {
                    user = new PersonalUser();
                    user.Id = reader.GetInt32(0);
                    user.Username = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.Password = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Question = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Answer = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Name = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    user.Province = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    user.District = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    user.TelephoneNumber = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    user.CellphoneNumber = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    user.MailAddress = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    user.Homepage = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    user.PostCode = !reader.IsDBNull(12) ? reader.GetString(12) : null;
                    user.Address = !reader.IsDBNull(13) ? reader.GetString(13) : null;
                    user.Gender = reader.GetInt32(14);
                    user.Nation = !reader.IsDBNull(15) ? reader.GetString(15) : null;
                    user.Birthday = reader.GetDateTime(16);
                    user.IsMarriage = reader.GetBoolean(17);
                    user.Height = reader.GetDouble(18);
                    user.Weight = reader.GetDouble(19);
                    user.Code = !reader.IsDBNull(20) ? reader.GetString(20) : null;
                    user.School = !reader.IsDBNull(21) ? reader.GetString(21) : null;
                    user.Knowledge = !reader.IsDBNull(22) ? reader.GetString(22) : null;
                    user.GraduateYear = reader.GetInt32(23);
                    user.MajorCategory = !reader.IsDBNull(24) ? reader.GetString(24) : null;
                    user.MajorName = !reader.IsDBNull(25) ? reader.GetString(25) : null;
                    user.ResumeUrl = !reader.IsDBNull(26) ? reader.GetString(26) : null;
                    user.CurrentStatu = !reader.IsDBNull(27) ? reader.GetString(27) : null;
                    user.ProfessionaldescriptionAndSelfevaluation = !reader.IsDBNull(28) ? reader.GetString(28) : null;
                    user.WorkexperienceAndDescription = !reader.IsDBNull(29) ? reader.GetString(29) : null;
                    user.HeadPic = !reader.IsDBNull(30) ? reader.GetString(30) : null;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get personal user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
            return user;
        }

        public bool UpdatePersonalUser(PersonalUser user)
        {
            const string SpName = "SpUpdatePersonalUser";

            try
            {
                object[] parameters = new object[]
                {
                    user.Username,
                    user.Name,
                    user.Province,
                    user.District,
                    user.TelephoneNumber,
                    user.CellphoneNumber,
                    user.MailAddress,
                    user.Homepage,
                    user.PostCode,
                    user.Address,
                    user.Code,
                    user.Gender,
                    user.Nation,
                    user.Birthday,
                    user.IsMarriage,
                    user.Height,
                    user.Weight,                    
                    user.School,
                    user.Knowledge,
                    user.GraduateYear,
                    user.MajorCategory,
                    user.MajorName,
                    user.ResumeUrl,
                    user.CurrentStatu,
                    user.ProfessionaldescriptionAndSelfevaluation,
                    user.WorkexperienceAndDescription,
                    user.HeadPic
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update PersonalUser", ex);
            }

            return true;
        }

        public bool CheckPersonalUser(string id, string password)
        {
            const string SpName = "SpCheckPersonalUserPassword";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    password
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o == null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in check password for PersonalUser",
                    ex);
            }

            return true;
        }

        public bool CheckPersonalUserId(string id)
        {
            const string SpName = "SpCheckPersonalUserId";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o == null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in check id for PersonalUser",
                    ex);
            }

            return true;
        }

        public int GetPersonalUserId(string id)
        {
            int userId = 0;
            const string SpName = "SpCheckPersonalUserId";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o != null)
                {
                    userId = Convert.ToInt32(o);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in check id for PersonalUser",
                    ex);
            }

            return userId;
        }

        public bool DeletePersonalUser(string id)
        {
            const string SpName = "SpDeletePersonalUser";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in delete PersonalUser", ex);
            }

            return true;
        }

        public bool UpdatePersonalHead(string id, string logoUrl)
        {
            const string SpName = "SpUploadPersonalHead";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    logoUrl
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update PersonalUser Head Pic", ex);
            }

            return true;
        }
    }
}
